package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class MhrmbonusLine extends X_hrm_bonusLine {

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 8012773645308120373L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (MhrmbonusLine.class);
	
	public MhrmbonusLine(Properties ctx, int hrm_bonusLine_ID, String trxName) {
		super(ctx, hrm_bonusLine_ID, trxName);
	}

	public MhrmbonusLine(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}
	
	@Override
	protected boolean afterSave(boolean newRecord, boolean success) {

		if(!newRecord){
			
			Mhrmreservesectional[] line = Mhrmreservesectional.getMhrmreservesectional(); 
			for(Mhrmreservesectional l: line){
				X_hrm_bonusline_sub bLine = MhrmbonusLine.getBonusLineAcct(gethrm_bonusLine_ID(), l.gethrm_reservesectional_ID());
				if(bLine == null)
					bLine = new X_hrm_bonusline_sub(Env.getCtx(),0,null);
				
				if(l.gethrm_reservesectional_ID() == Mhrmreserve.RESERVE_SECTIONAL_CHARGE){
					bLine.sethrm_amount(gethrm_total());
					bLine.sethrm_reservesectional_ID(Mhrmreserve.RESERVE_SECTIONAL_CHARGE);
				}
				else if(l.gethrm_reservesectional_ID() == Mhrmreserve.RESERVE_SECTIONAL_CNALOG){
					bLine.sethrm_amount(gethrm_CNalog());
					bLine.sethrm_reservesectional_ID(Mhrmreserve.RESERVE_SECTIONAL_CNALOG);
				}
				else if(l.gethrm_reservesectional_ID() == Mhrmreserve.RESERVE_SECTIONAL_COTCH){
					bLine.sethrm_amount(gethrm_COtch());
					bLine.sethrm_reservesectional_ID(Mhrmreserve.RESERVE_SECTIONAL_COTCH);
				}
				bLine.sethrm_reserve_ID(Mhrmreserve.RESERVE_BONUS);
				bLine.sethrm_bonus_ID(gethrm_bonus_ID());
				bLine.sethrm_bonusLine_ID(gethrm_bonusLine_ID());
				bLine.saveEx();
			}	
		}
		
		return true;
	}
	

	/**
	 * Search for entries in the bonus line
	 * 
	 * @param C_BPartner_ID
	 * @param hrm_bonus_ID
	 * @return  Model bonus line
	 */
	public static MhrmbonusLine getBonusline(int C_BPartner_ID, int bonus_id, Timestamp dt) {
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		MhrmbonusLine hrmbl = null;
			
		// 
		String sql_ = "SELECT * FROM HRM_BONUSLINE \n" +
						"WHERE ISACTIVE='Y' \n"  +
							" AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
							" AND C_BPARTNER_ID=" + C_BPartner_ID                     + " \n" +
							" AND HRM_BONUS_ID=" + bonus_id + " \n" +
							" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
							" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					hrmbl = new MhrmbonusLine(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			
		return hrmbl;
		
	}
	
	
	public static BigDecimal getAverageBonus(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return null;
				
		BigDecimal result = new BigDecimal(0);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// Employee data from the card
		String sql_ = "SELECT SUM(HRM_AMOUNTBONUS) FROM HRM_BONUSLINE \n" +
						"WHERE ISACTIVE='Y' \n" +
						"AND ISAVERAGESUM='Y' \n" +
						"AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
						"AND C_BPARTNER_ID=" + C_BPartner_ID                     + " \n" +
						" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', "+DB.TO_DATE(dt, true)+") " +
						" AND DATE_TRUNC('MONTH',"+DB.TO_DATE(dt, true)+") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL"; 
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}
	
public static BigDecimal getBonusSum(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return null;
				
		BigDecimal result = new BigDecimal(0);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// Employee data from the card
		String sql_ = "SELECT SUM FROM HRM_BONUSSUM_V \n" +
						" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						 " AND C_BPARTNER_ID="  + C_BPartner_ID                     + " \n " +
						 " AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
						 " AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}

/**
 * Search for entries in the bonus line array
 * 
 * @param C_BPartner_ID
 * @return  Model bonus line
 */
	public static MhrmbonusLine[] getBonusArray(int C_BPartner_ID, Timestamp dt) {
		
		if (C_BPartner_ID == 0)
			return null;
	//
    PreparedStatement pstmt = null;
	ResultSet rs = null;
	MhrmbonusLine result = null;
	
	ArrayList<MhrmbonusLine> list = new ArrayList<MhrmbonusLine>();
		
	// 
	String sql_ = "SELECT * FROM HRM_BONUSLINE \n " +
					"WHERE ISACTIVE='Y' \n "  +
						" AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						" AND C_BPARTNER_ID=" + C_BPartner_ID                     + " \n " +
						" AND DATEFROM BETWEEN DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") \n " +
						" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();	
			while(rs.next()){
				result = new MhrmbonusLine(Env.getCtx(), rs, null);
				list.add(result);
			}				
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
	return list.toArray(new MhrmbonusLine[list.size()]);
	
	}
	
	/**
	 * Search for entries in the -->>> vacationLine <<---
	 * 
	 * @param C_BPartner_ID
	 * @param Timestamp dt
	 * @return  Model bonus line
	 */
	public static X_hrm_bonusline_sub getBonusLineAcct(int bonusLine_ID, int reservesectional_ID) {
		
		if (bonusLine_ID == 0)
			return null;
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		X_hrm_bonusline_sub result = null;
			
		// 
		String sql_ = "SELECT * FROM HRM_BONUSLINE_SUB WHERE HRM_BONUSLINE_ID= " + bonusLine_ID +" \n" +
				" AND HRM_RESERVESECTIONAL_ID=" + reservesectional_ID;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					result = new X_hrm_bonusline_sub(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
		return result;		
	}
	
	/**
	 * Search for entries in the bonus line
	 * 
	 * @param C_BPartner_ID
	 * @param hrm_bonus_ID
	 * @return  Model bonus line
	 */
	public static X_hrm_bonusline_sub getSubline(int ctx, int bonus_id, int bonusline_id) {
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		X_hrm_bonusline_sub result = null;
			
		// 
		String sql_ = "SELECT * FROM HRM_BONUSLINE_SUB \n" +
						"WHERE ISACTIVE='Y' \n"  +
							" AND AD_CLIENT_ID="  + ctx + " \n" +
							" AND HRM_BONUS_ID=" + bonus_id + " \n" +
							" AND HRM_BONUSLINE_ID=" + bonusline_id;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					result = new X_hrm_bonusline_sub(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			
		return result;		
	}
	
	public static X_hrm_bonusline_sub getline(int hrm_bonus_id, int C_BPartner_ID, int C_Charge_ID){
		
		if (hrm_bonus_id==0)
			return null;

		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		X_hrm_bonusline_sub result = null;
			
		// 
		String sql_ = "SELECT * FROM HRM_BONUSLINE_SUB \n " +
						"WHERE ISACTIVE='Y' \n " +
//							"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							"AND C_CHARGE_ID=" + C_Charge_ID + " \n " +
							"AND hrm_bonus_id=" + hrm_bonus_id + " \n " +
							"AND C_BPartner_ID =" + C_BPartner_ID ;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					result = new X_hrm_bonusline_sub(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return result;		
	}

}
